package com.vkserver.services;

import com.vkserver.services.utils.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author roman.prudkogliad@gmail.com
 */
public class DBManager {

    private static DBManager dbManager;
    private Connection connection;
    private String dburl = "jdbc:mysql://localhost:3306/vkschema?encoding=UTF-8&amp;useUnicode=true&amp;characterEncoding=UTF-8";
    private String pass = "r1pd1k";
    private String login = "root";

    private DBManager() {
        //ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml", DBManager.class);
        //DataSource dataSource = (DataSource) ac.getBean("mysqlDataSource");
        try {
            String driver = "com.mysql.jdbc.Driver";
            Class.forName(driver).newInstance();
            Properties properties = new Properties();
            properties.setProperty("user", login);
            properties.setProperty("password", pass);
            properties.setProperty("useUnicode", "true");
            properties.setProperty("characterEncoding", "UTF-8");
            connection = DriverManager.getConnection(dburl, properties);
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (InstantiationException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (IllegalAccessException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    public static synchronized DBManager getInstance() {
        if (dbManager == null) {
            dbManager = new DBManager();
        }
        return dbManager;
    }

    public synchronized User addUser(User user) {
        PreparedStatement query = null;
        try {
            query = connection.prepareStatement(DBUtil.REGISTER_USER);
            query.setLong(1, user.getUserId());
            query.setInt(2, user.getSex());
            query.setString(3, user.getFIO());
            query.execute();
            ResultSet resultSet = query.getResultSet();
            while (resultSet.next()) {
                System.out.println("DB name: " + resultSet.getString("FIO"));
            }
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            if (query != null) {
                try {
                    query.close();
                } catch (SQLException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
            }
        }
        return user;
    }

    public synchronized String addQuestion(Question question) {
        PreparedStatement query = null;
        try {
            query = connection.prepareStatement(DBUtil.QUESTION_INSERT);
            query.setLong(1, question.getUserId());
            query.setString(2, question.getText());
            query.execute();
            ResultSet questionId = query.getResultSet();
            while(questionId.next()){
                return "" + questionId.getInt("question_id");
            }
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            if (query != null) {
                try {
                    query.close();
                } catch (SQLException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
            }
        }
        return "OK";
    }

    public synchronized String addAnswer(Answer answer) {
        PreparedStatement query = null;
        try {
            query = connection.prepareStatement(DBUtil.ANSWER_INSERT);
            query.setLong(1, answer.getUserId());
            query.setLong(2, answer.getQuestionId());
            query.setString(3, answer.getText());
            query.execute();
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            if (query != null) {
                try {
                    query.close();
                } catch (SQLException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
            }
        }
        return "OK";
    }

    public synchronized String answerLike(long answerId) {
        PreparedStatement query = null;
        try {
            query = connection.prepareStatement(DBUtil.ANSWER_LIKE);
            query.setLong(1, answerId);
            query.execute();
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            if (query != null) {
                try {
                    query.close();
                } catch (SQLException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
            }
        }
        return "OK";
    }

    public synchronized List<Question> getQuestions(long userId) {
        List<Question> q = new ArrayList<Question>();
         PreparedStatement query = null;
        try {
            query = connection.prepareStatement(DBUtil.GET_ALL_QUESTIONS);
            query.setLong(1, userId);
            query.execute();
            ResultSet questionId = query.getResultSet();
            while(questionId.next()){
                Question question = new Question();
                question.setQuestionId(questionId.getInt("question_id"));
                question.setText(questionId.getString("text"));
                q.add(question);
            }
            return q;
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            if (query != null) {
                try {
                    query.close();
                } catch (SQLException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
            }
        }
        return q;
    }

    public synchronized List<Answer> getAllAnswers(long questionId) {
        List<Answer> answerList = new ArrayList<Answer>();
         PreparedStatement query = null;
        try {
            query = connection.prepareStatement(DBUtil.GET_ALL_ANSWERS);
            query.setLong(1, questionId);
            query.execute();
            ResultSet answerResults = query.getResultSet();
            while(answerResults.next()){
                Answer answer = new Answer();
                answer.setAnswerId(answerResults.getInt("answer_id"));
                answer.setText(answerResults.getString("text"));
                answerList.add(answer);
            }
            return answerList;
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            if (query != null) {
                try {
                    query.close();
                } catch (SQLException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                }
            }
        }
        return answerList;
    }


    public synchronized StatisticData getStatistic(long userId, int sex) {
        StatisticData statisticData = new StatisticData();
        try {

            ResultSet last_questions = executeProcedure(DBUtil.LAST_SEVEN_QUESTIONS, "" + userId);
            System.out.println("ResultSet " + last_questions);
            List<Question> lastQuestionsList = new ArrayList<Question>();
            while (last_questions.next()) {
                Question q = new Question();
                q.setQuestionId(last_questions.getInt("question_id"));
                q.setText(last_questions.getString("text"));
                System.out.println("question "+ q);
                lastQuestionsList.add(q);
            }
            statisticData.setLastQuestions(lastQuestionsList);

            ResultSet topLikeUsers = executeProcedure(sex == 0 ? DBUtil.TOP_LIKE_USERS_WOMAN : DBUtil.TOP_LIKE_USERS_MAN, null);
            List<User>  topLikeUsersList = new ArrayList<User>();
            while (topLikeUsers.next()){
                User u = new User();
                u.setUserId(topLikeUsers.getInt("user_id"));
                u.setFIO(topLikeUsers.getString("FIO"));
                System.out.println("user " + u);
                topLikeUsersList.add(u);
            }
            statisticData.setTopLikeUsers(topLikeUsersList);

            ResultSet topAnswerUsers = executeProcedure(sex == 0 ? DBUtil.TOP_ANSWER_USERS_WOMAN : DBUtil.TOP_ANSWER_USERS_MAN, null);
            List<User> topAnswerUsersList = new ArrayList<User>();
            while(topAnswerUsers.next()){
                User u = new User();
                u.setUserId(topAnswerUsers.getInt("user_id"));
                u.setFIO(topAnswerUsers.getString("FIO"));
                System.out.println("user " + u);
                topAnswerUsersList.add(u);
            }
            statisticData.setTopActiveUsers(topAnswerUsersList);

            ResultSet topQuestions = executeProcedure(sex == 0 ? DBUtil.TOP_QUESTIONS_WOMAN : DBUtil.TOP_QUESTIONS_MAN, null);
            List<Question> topQuestionsList = new ArrayList<Question>();
            while (topQuestions.next()){
                Question q = new Question();
                q.setQuestionId(topQuestions.getInt("question_id"));
                q.setText(topQuestions.getString("text"));
                System.out.println("user " + q);
                topQuestionsList.add(q);
            }
            statisticData.setTopQuestions(topQuestionsList);

            ResultSet chatCount = executeProcedure(DBUtil.USER_DISCUSSIONS, "" + userId);
            while(chatCount.next()){
                statisticData.setUserQuestions(chatCount.getInt("Count_quest"));
            }

            ResultSet infoActiveStat = executeProcedure(DBUtil.USER_ACTIVE, null);
            while (infoActiveStat.next()){
                statisticData.setActivityQuestionsMan("" + infoActiveStat.getDouble("quest_m"));
                statisticData.setActivityQuestionsWoman("" + infoActiveStat.getDouble("quest_f"));
                statisticData.setActivityAnswersMan("" + infoActiveStat.getDouble("answer_m"));
                statisticData.setActivityAnswersWoman("" + infoActiveStat.getDouble("answer_f"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("StatisticData" + statisticData);

        return statisticData;
    }

    private ResultSet executeProcedure(String procedure, String... params) {
        PreparedStatement query = null;
        try {
            query = connection.prepareStatement(procedure);

            if (params != null){
                int i = 1;
            for (String param : params) {
                query.setString(i++, param);
            }
            }
            System.out.println("Query " + query);
            query.execute();
            return query.getResultSet();
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            if (query != null) {
                /*try {
                    //query.close();
                } catch (SQLException e) {
                    e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                } */
            }
        }
        return null;
    }

}
